package my.db;

import java.sql.*;
import java.util.*;
import java.lang.reflect.*;

import javax.sql.DataSource;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.sina.sae.util.SaeUserInfo;
import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * 数据库管理
 * 
 * @author Winter Lau
 * @date 2010-2-2 下午10:18:50
 */
public class DBManager {

	private final static Log log = LogFactory.getLog(DBManager.class);
	private final static ThreadLocal<Connection> conns = new ThreadLocal<Connection>();
	private static DataSource dataSource;
	private static boolean show_sql = true;
	private static Properties cp_props = new Properties();

	public static String datasource = "com.mchange.v2.c3p0.ComboPooledDataSource";
	public static String driverClass = "com.mysql.jdbc.Driver";
	public static String url;
	public static String username;
	public static String password;

	static {
		initDataSource(null);
	}

	/**
	 * 初始化连接池
	 * # DataSource
		jdbc.datasource=com.mchange.v2.c3p0.ComboPooledDataSource
		jdbc.show_sql=true
		# Database Configurations
		jdbc.driverClass=com.mysql.jdbc.Driver
		jdbc.jdbcUrl=jdbc:mysql://localhost:3306/oschina
		jdbc.user=root
		jdbc.password=moabc
		jdbc.maxPoolSize=100
		jdbc.minPoolSize=2
		jdbc.initialPoolSize=2
		jdbc.acquireIncrement=2
		jdbc.maxStatements=1000
		jdbc.maxIdleTime=300
		jdbc.checkoutTimeout=5000
	 * @param props
	 * @param show_sql
	 */
	private final static void initDataSource(Properties dbProperties) {
		try {
			username = SaeUserInfo.getAccessKey();
			password = SaeUserInfo.getSecretKey();
			String appname = SaeUserInfo.getAppName();
			url = "jdbc:mysql://r.rdc.sae.sina.com.cn:3307/app_" + appname
					+ "?autoReconnect=true";
			
			cp_props.setProperty("datasource", datasource);
			cp_props.setProperty("driverClass", driverClass);
			cp_props.setProperty("jdbcUrl", url);
			cp_props.setProperty("user", username);
			cp_props.setProperty("password", password);
/*			cp_props.setProperty("jdbcUrl", "jdbc:mysql://localhost:3306/meow");
			cp_props.setProperty("user", "root");
			cp_props.setProperty("password", "1990212daisy");
*/			cp_props.setProperty("maxPoolSize", "100");
			cp_props.setProperty("minPoolSize", "2");
			cp_props.setProperty("initialPoolSize", "2");
			cp_props.setProperty("acquireIncrement", "2");
			cp_props.setProperty("maxStatements", "1000");
			cp_props.setProperty("maxIdleTime", "300");
			cp_props.setProperty("checkoutTimeout", "5000");
			dataSource = (DataSource) new ComboPooledDataSource();
			if (dataSource.getClass().getName().indexOf("c3p0") > 0) {
				// Disable JMX in C3P0
				System.setProperty(
						"com.mchange.v2.c3p0.management.ManagementCoordinator",
						"com.mchange.v2.c3p0.management.NullManagementCoordinator");
			}
			log.info("Using DataSource : " + dataSource.getClass().getName());
			BeanUtils.populate(dataSource, cp_props);

			Connection conn = getConnection();
			DatabaseMetaData mdm = conn.getMetaData();
			log.info("Connected to " + mdm.getDatabaseProductName() + " "
					+ mdm.getDatabaseProductVersion());
			closeConnection();
		} catch (Exception e) {
			e.printStackTrace();
			throw new DBException(e);
		}
	}

	public static class PoolStatus {
		public int total = -1; // 总连接数
		public int busy = -1; // 活动连接
		public int idle = -1; // 空闲连接

		public int getTotal() {
			return total;
		}

		public int getBusy() {
			return busy;
		}

		public int getIdle() {
			return idle;
		}
	}

	public static PoolStatus getPoolStatus() {
		PoolStatus pool = new PoolStatus();

		try {
			pool.total = (Integer) PropertyUtils.getProperty(dataSource,
					"numConnectionsDefaultUser");
			pool.busy = (Integer) PropertyUtils.getProperty(dataSource,
					"numBusyConnectionsDefaultUser");
			pool.idle = (Integer) PropertyUtils.getProperty(dataSource,
					"numIdleConnectionsDefaultUser");
		} catch (Exception e) {
		}
		return pool;
	}

	/**
	 * 断开连接池
	 */
	public final static void closeDataSource() {
		try {
			dataSource.getClass().getMethod("close").invoke(dataSource);
		} catch (NoSuchMethodException e) {
		} catch (Exception e) {
			log.error("Unabled to destroy DataSource!!! ", e);
		}
	}

	public final static Connection getConnection() throws SQLException {
		Connection conn = conns.get();
		if (conn == null || conn.isClosed()) {
			conn = _getConnection();
			conns.set(conn);
		}
		return (show_sql && !Proxy.isProxyClass(conn.getClass())) ? new _DebugConnection(
				conn).getConnection() : conn;
	}

	private static Connection _getConnection() throws SQLException {
		try {
			return dataSource.getConnection();
		} catch (Exception e) {
			return DriverManager.getConnection(cp_props.getProperty("jdbcUrl"),
					cp_props.getProperty("user"),
					cp_props.getProperty("password"));
		}
	}

	/**
	 * 关闭连接
	 */
	public final static void closeConnection() {
		Connection conn = conns.get();
		try {
			if (conn != null && !conn.isClosed()) {
				conn.setAutoCommit(true);
				conn.close();
			}
		} catch (SQLException e) {
			log.error("Unabled to close connection!!! ", e);
		}
		conns.set(null);
	}

	/**
	 * 用于跟踪执行的SQL语句
	 * 
	 * @author liudong
	 */
	static class _DebugConnection implements InvocationHandler {

		private final static Log log = LogFactory
				.getLog(_DebugConnection.class);

		private Connection conn = null;

		public _DebugConnection(Connection conn) {
			this.conn = conn;
		}

		/**
		 * Returns the conn.
		 * 
		 * @return Connection
		 */
		public Connection getConnection() {
			return (Connection) Proxy.newProxyInstance(conn.getClass()
					.getClassLoader(), conn.getClass().getInterfaces(), this);
		}

		public Object invoke(Object proxy, Method m, Object[] args)
				throws Throwable {
			try {
				String method = m.getName();
				if ("prepareStatement".equals(method)
						|| "createStatement".equals(method))
					log.info("[SQL] >>> " + args[0]);
				return m.invoke(conn, args);
			} catch (InvocationTargetException e) {
				throw e.getTargetException();
			}
		}

	}

}
